シェルスクリプトでBigQueryのテーブルのデータをCSVにエクスポートする

#Google Cloud (GCP)

#Google BigQuery

根本夏瑠

2024.06.30

データアナリティクス事業本部の根本です。6月から30度以上の日ばかりで参ってしまう日々です。7月、8月はどうなってしまうのか心配になってしまいます。
さて、最近シェルスクリプトでどうしてもBigQueryのデータを出力する必要があったので実装してみました。シンプルな実装でしたが、bq exportコマンドについて理解を深める良い機会だったので記事にしてみました。

やりたいこと

指定テーブルを順次CSVに出力したい
出力するCSVはテーブルごとに1ファイルにしたい
テーブルには出力対象以外のデータも含まれているため、出力対象だけに絞りたい
ファイル名は指定したものにしたい(テーブル名_日付.csvのような)
出力ファイルはCloud Storageにエクスポートしたい

上記の要件で、シェルスクリプトを実装しました。
またBigQueyでCloud Storageにエクスポートする場合、データ量が1GBを超えると複数ファイルに分割されます。今回シェルスクリプトで扱うデータは1GB未満の想定です。

実装を見てみる

とりあえずシェルスクリプト全文です。

#!/bin/bash
DATASET_ID="データセットID"
BUCKET_NAME="出力先バケット名"
PROJECT_ID="プロジェクトID"
TABLE_ID_LIST=("出力対象テーブル①" "出力対象テーブル②")
    for TABLE_ID in "${TABLE_ID_LIST[@]}"; do
        echo "Exporting table: ${TABLE_ID}"
        # BigQueryテーブル作成
        bq query --use_legacy_sql=false "CREATE TABLE \`$PROJECT_ID\`.\`$DATASET_ID\`.\`temp_${TABLE_ID}\` AS SELECT * FROM \`$PROJECT_ID\`.\`$DATASET_ID\`.\`$TABLE_ID\` WHERE 1 = 1;"
        # GCSへエクスポート
        bq extract --location=asia-northeast1 \
            --destination_format CSV \
            --compression NONE \
            --field_delimiter ',' \
            --print_header=true \
            $PROJECT_ID:$EXPORT_DATASET_ID.temp_${TABLE_ID} \
            gs://$BUCKET_NAME/${TABLE_ID}_export.csv
        bq query --use_legacy_sql=false "DROP TABLE \`$PROJECT_ID\`.\`$DATASET_ID\`.\`temp_${TABLE_ID}\`;"
    done

上記のスクリプトは、テーブルを中間テーブルにコピーし、その中間テーブルをbq extractコマンドでCloud StorageにCSV形式でエクスポートし、最後に中間テーブルを削除するという一連の処理を行います。
TABLE_ID_LISTの配列要素数だけファイルを出力することができます。ただし、直列実行となるので容量が大きいテーブルのエクスポートをたくさん実行する場合には向いていません。 ※上記のスクリプトではCTASする際に、WEHERE句が1=1になっていたり、列名指定が*になっていますが例示するための都合上であり、実際には列名指定＋WHERE句で抽出条件を設定しています。

bq extractコマンドについて

bq extractコマンドはテーブルのデータを Cloud Storage にエクスポートするために使用します。いくつかポイントになるフラグがあるので例示します。

フラグ名	設定値
圧縮形式(compression)	GZIP、DEFLATE、SNAPPY、NONE(デフォルト値)
出力フォーマット(destination_format)	NEWLINE_DELIMITED_JSON、AVRO、PARQUET、CSV(デフォルト値)
field_delimiter	出力フォーマットがCSVの場合指定
BigQueryリソース指定形式	PROJECT:DATASET.TABLE(bqコマンドではプロジェクトとデータセットの区切りが[:]なので注意

上記を踏まえ今回使ったbq extractコマンドを見ていきます。

bq extract --location=asia-northeast1 \
            --destination_format CSV \
            --compression NONE \
            --field_delimiter ',' \
            --print_header=true \
            $PROJECT_ID:$EXPORT_DATASET_ID.temp_${TABLE_ID} \
            gs://$BUCKET_NAME/${TABLE_ID}_export.csv

上記コマンドの場合、以下の情報が設定されています。

フラグ名	内容
lopcation	ロケーション
destination_format	ファイルフォーマット
compression	圧縮
print_header	ヘッダ出力有無。trueなら出力
field_delimiter	CSV でエクスポートする場合の出力ファイルの列の区切り文字
$PROJECT_ID:$EXPORT_DATASET_ID.temp_${TABLE_ID}	テーブルの保存先
gs://$BUCKET_NAME/${TABLE_ID}_export.csv	出力先情報(バケットパス)

上記の設定で実行すると、指定テーブルを指定したファイル名(上記の場合なら[テーブル名_export.csv])でCSV形式でヘッダー付きで出力できます。(出力データが1GB以上になる場合、連番が付与されます)
ただ、テーブルデータ全件＋全列の出力となるので今回のスクリプトでは中間テーブルにデータをCTASして必要なデータだけにして出力しています。

bq extractコマンドの注意点

お手軽にテーブルのデータをエクスポートできるbq extractコマンドですが、何点か注意点があります - テーブル全件の出力となる
- 列指定、条件設定はできない
- 出力データが1GB以上になる場合はファイル名に連番が付与された状態で分割出力されます

まとめ

BigQueryからのデータエクスポートに関しては他にもEXPORT DATAステートメントを用いたり、Bigtableにエクスポートしたりなどさまざまな方法があります。
どういった手法を取るかはワークロードによりけりなので、その時に最適な方法を検討して選択できるようにしたいところです。
この記事がどなたかのお役に立てば嬉しいです。それではまた。